with tab_waybill as ( --从订单表中取运单信息信息（运单号、取件网点、派件网点）
    select
        waybill_no,pick_network_code,terminal_dispatch_code,dispatch_network_code,input_time
    from
        (
            select
                waybill_id as waybill_no
                 ,real_pick_network_code as  pick_network_code
                 ,terminal_dispatch_code
                 ,dispatch_network_code
                 ,input_time
                 ,ROW_NUMBER() over(partition by waybill_id order by dt desc ) rk
            from jms_dwd.dwd_yl_oms_oms_order_hf
            where substr(dt,1,10)<=date_add('{{ execution_date |  cst_ds }}',0)
              and substr(dt,1,10)>=date_add('{{ execution_date |  cst_ds }}',-7)
              and waybill_rnk=1
        )t where rk=1 --and pick_network_code is not null
             and dispatch_network_code is not NULL
),
     tab_taking as (--取运单揽收时间
         select
             waybill_no,scan_time,scan_date,scan_site_code
         from
             (
                 select
                     waybill_no,
                     scan_time,
                     date(scan_time) scan_date,
             scan_site_code,
             ROW_NUMBER() over(partition by waybill_no,scan_site_code order by scan_time) rk
from jms_dwd.dwd_tab_barscan_taking_base_hi
where substr(dt,1,10) between date_add('{{ execution_date |  cst_ds }}',-6) and date_add('{{ execution_date |  cst_ds }}',0)
    )t where rk=1 and date(scan_time) ='{{ execution_date |  cst_ds }}'
    ),

    tab_waybill_weight as (--内部计费重量取值逻辑：始发网点揽收入仓、始发集散到件、始发中心到件4个重量中取最大值,作为内部计费重量
select
    waybill_no ,if(max(nvl(weight,0))>=max(nvl(vo_weight,0)),max(nvl(weight,0)),max(nvl(vo_weight,0))) package_inside_charge_weight
from
    (---入仓扫描
    select waybill_no ,weight ,len_wid_hei_product/8000  as vo_weight
    from jms_dwd.dwd_tab_barscan_warehousing_base_hi
    where substr(dt,1,10) between date_add('{{ execution_date |  cst_ds }}',-1) and date_add('{{ execution_date |  cst_ds }}',0)
    union all --揽收扫描
    select waybill_no ,weight, len_wid_hei_product/8000 as vo_weight
    from jms_dwd.dwd_tab_barscan_taking_base_hi
    where substr(dt,1,10) between date_add('{{ execution_date |  cst_ds }}',-1) and date_add('{{ execution_date |  cst_ds }}',0)
    union all--始发集散、中心到件
    select waybill_no ,weight,vo_weight from (
    select
    waybill_no ,weight ,len_wid_hei_product/8000  as vo_weight,
    ROW_NUMBER() over(partition by waybill_no,scan_type order by scan_time,scan_type) rk
    from jms_dwd.dwd_tab_barscan_arrival_base_hi t
    inner join jms_dim.dim_network_whole_massage net on (net.network_type =4 or net.network_type = 5 ) and net.code= t.scan_site_code
    where substr(t.dt,1,10) between date_add('{{ execution_date |  cst_ds }}',-1) and date_add('{{ execution_date |  cst_ds }}',0)
    )t where rk=1
    )t group by waybill_no
    )
insert overwrite table jms_dm.dm_waybill_simple_info_dt partition(dt)
--将运单信息补充完整（运单号、取件网点、派件网点）+（内部计费重量,揽收时间）
select
    tab_waybill.waybill_no,
    tab_waybill.pick_network_code,
    tab_waybill.terminal_dispatch_code,
    tab_waybill.dispatch_network_code,
    tab_waybill_weight.package_inside_charge_weight,
    tab_taking.scan_time as taking_scan_time,
    nvl(tab_taking.scan_date,date(tab_waybill.input_time)) as taking_scan_date,
    date_format(tab_taking.scan_time,'HH:mm')  as taking_scan_hs,
    nvl(tab_taking.scan_date,date(tab_waybill.input_time)) as dt
from tab_waybill
    left join tab_waybill_weight on tab_waybill_weight.waybill_no=tab_waybill.waybill_no
    left join tab_taking on tab_taking.waybill_no=tab_waybill.waybill_no and tab_taking.scan_site_code=tab_waybill.pick_network_code
where tab_taking.scan_date='{{ execution_date |  cst_ds }}' or date(tab_waybill.input_time)='{{ execution_date |  cst_ds }}'
    distribute by dt,pmod(hash(waybill_no),50)
;